﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Text;
using System.Web;

namespace Raffles.OA.Utility
{
    public class NPOIHelper
    {
        /// <summary>
        /// 列宽自适应
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="cols"></param>
        private static void AutoColumnWidth(ISheet sheet, int cols)
        {
            for (int col = 0; col <= cols; col++)
            {
                int columnWidth = sheet.GetColumnWidth(col) / 256;//获取当前列宽度
                for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row.GetCell(cols) != null)
                    {
                        ICell cell = row.GetCell(col);
                        int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
                        columnWidth = columnWidth < contextLength ? contextLength : columnWidth;
                    }
                }
                //以下两个放置在最后，基于EXCEL有数据之后才会生效
                sheet.AutoSizeColumn(col);//自适应宽度，但是其实还是比实际文本要宽
                sheet.SetColumnWidth(col, columnWidth * 200);

            }
        }
        /// <summary>
        /// DataTable导出Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columns">自定义表头列</param>
        /// <param name="strSheetName">工作簿名</param>
        public static MemoryStream ExportExcel(DataTable dt, ArrayList columns, string strFileName, string strSheetName)
        {
            IWorkbook book = null;
            if (strFileName.IndexOf(".xlsx") > 0) // 2007版本
            {
                book = new XSSFWorkbook();
            }
            else if (strFileName.IndexOf(".xls") > 0) // 2003版本
            {
                book = new HSSFWorkbook();
            }
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            ICellStyle styleHead = book.CreateCellStyle();
            styleHead.Alignment = HorizontalAlignment.Center;
            styleHead.VerticalAlignment = VerticalAlignment.Center;
            IFont font = book.CreateFont();
            font.IsBold = true;
            styleHead.SetFont(font);
            int sheetNum = 1;
            int tempIndex = 1;
            ISheet sheet = book.CreateSheet($"{strSheetName}-{sheetNum}");
            IRow dataRow = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i == 0)
                {
                    dataRow = sheet.CreateRow(0);
                    for (int k = 0; k < columns.Count; k++)
                    {
                        dataRow.CreateCell(k).SetCellValue(columns[k].ToString());
                        dataRow.GetCell(k).CellStyle = styleHead;
                    }
                }
                
                dataRow = sheet.CreateRow(tempIndex);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string ValueType = "";
                    string Value = "";
                    if (dt.Rows[i][j].ToString() != null)
                    {
                        ValueType = dt.Rows[i][j].GetType().ToString();
                        Value = dt.Rows[i][j].ToString();
                    }
                    switch (ValueType)
                    {
                        case "System.String"://字符串类型
                            dataRow.CreateCell(j).SetCellValue(Value);
                            break;

                        case "System.DateTime"://日期类型
                            System.DateTime dateV;
                            System.DateTime.TryParse(Value, out dateV);
                            dataRow.CreateCell(j).SetCellValue(dateV.ToString("yyyy-MM-dd HH:mm:ss"));
                            break;

                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(Value, out boolV);
                            dataRow.CreateCell(j).SetCellValue(boolV);
                            break;

                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(Value, out intV);
                            dataRow.CreateCell(j).SetCellValue(intV);
                            break;

                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(Value, out doubV);
                            dataRow.CreateCell(j).SetCellValue(doubV);
                            break;

                        case "System.DBNull"://空值处理
                            dataRow.CreateCell(j).SetCellValue("");
                            break;

                        default:
                            dataRow.CreateCell(j).SetCellValue("");
                            break;
                    }
                    dataRow.GetCell(j).CellStyle = style;
                    //设置宽度
                    sheet.SetColumnWidth(j, (Value.Length + 10) * 256);
                }
                if (tempIndex == 65535)
                {
                    sheetNum++;
                    sheet = book.CreateSheet(strSheetName + sheetNum);
                    dataRow = sheet.CreateRow(0);
                    for (int k = 0; k < columns.Count; k++)
                    {
                        dataRow.CreateCell(k).SetCellValue(columns[k].ToString());
                        dataRow.GetCell(k).CellStyle = styleHead;
                    }
                    tempIndex = 0;
                }
                tempIndex++;
            }
            AutoColumnWidth(sheet, columns.Count);
            using (MemoryStream ms = new MemoryStream())
            {
                book.Write(ms);
                ms.Flush();
                ms.Position = 0;
                book.Close();
                return ms;
            }
        }
        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        public static void ExportByWeb(DataTable dtSource, ArrayList columns, string strFileName,string strSheetName)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            curContext.Response.BinaryWrite(ExportExcel(dtSource, columns, strFileName,strSheetName).GetBuffer());
            curContext.Response.End();
        }
    }
}